package com.corecloud.mapper.mysql;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


/**
 * 其余综合查询公用接口
 * 
 * @author CCW
 * @email 452050507@qq.com
 * @date 2020-09-11 15:00:18
 */
@Mapper
public interface OtherBaseMapper {


	/**
	 * 根据用户ID获取对应生效的权限信息
	 * @return
	 */
	@Select({"<script>select " +
			"(select count(1) from sys_dept where is_valid = 1) as deptNum," +
			"(select count(1) from sys_user where is_valid = 1) as userNum," +
			"(select count(1) from sys_app where is_valid = 1) as appNum," +
			"(select count(1) from sys_log where DATE_FORMAT(create_time,'%Y-%m-%d') = current_date) as todayLogNum," +
			"(select count(1) from sys_log) as allLogNum " +
			"</script>"})
	Map<String, Object> getWelcomePageData();


	/**
	 * 首页地图数据源
	 * @return
	 */
	@Select({"<script>" +
			"SELECT " +
			"sa.area_name,sa.area_id,sum(sa1.dept_num) as dept_num,sum(sa1.user_num) as user_num " +
			"FROM " +
			"sys_area sa " +
			"left join (" +
			"SELECT sa.area_id,sd.dept_num,su.user_num FROM sys_area AS sa " +
			"left join (select area_id,count(area_id) as dept_num from sys_dept where area_id is not null GROUP BY area_id ) sd on sd.area_id = sa.area_id " +
			"left join (select area_id,count(area_id) as user_num from sys_user where area_id is not null GROUP BY area_id) su on su.area_id = sa.area_id " +
			"group by sa.area_id " +
			") sa1 on sa1.area_id = sa.area_id " +
			"where (sa.area_parent_id = '410000' or sa.area_id = '410000') and dept_num is not null " +
			"GROUP BY " +
			"sa.area_id" +
			"</script>"})
	List<Map<String,Object>> getWelcomeMapData();



	/**
	 * 首页日志统计数据源
	 * @return
	 */
	@Select({"<script>select a.create_time,count(a.role_name) as role_num,a.role_name from ( " +
			"SELECT " +
			"sl.user_name, " +
			"DATE_FORMAT(sl.create_time,'%m-%d') as create_time, " +
			"sur.role_name  " +
			"FROM " +
			"sys_log sl " +
			"LEFT JOIN sys_user su ON su.user_name = sl.user_name  " +
			"left join sys_user_role sur on sur.user_id = su.id  " +
			"WHERE " +
			"sl.create_time &lt; date_add(DATE_FORMAT(NOW(),'%y-%m-%d'),interval 1 day) " +
			"and sl.create_time &gt; date_sub(DATE_FORMAT(curdate(),'%y-%m-%d'),interval 8 day) " +
			"and sl.user_name IS NOT NULL " +
			"and su.user_name is not null  " +
			") a  " +
			"group by a.create_time,a.role_name</script>"})
	List<Map<String,Object>> getWelcomeLogData();

}
